﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace shopform
{
    class Project
    {
        public String ProjectID;
        public String ProjectName;
        public String ProjectCity;
        public String ProjectAddr;
        public String CompanyReferentMail;
        public String CompanyID;
        public String DateCreated;

        /// <summary>
        /// Get all projects with filter.
        /// </summary>
        /// <param name="pName">Name like this.</param>
        /// <param name="isDone">Is the project done (1/0)?</param>
        /// <returns></returns>
        public static DataTable getAllProjectsWithNameLike(string pName,int isDone)
        {
            try
            {
                String query =
                    "SELECT ProjectID, CompanyName, ProjectName, ProjectCity, ProjectAddr, " +
                    "CompanyReferentName, DateCreated " +
                    "FROM Projects,Companies,CompanyReferents " +
                    "WHERE Projects.CompanyID = Companies.CompanyID AND " +
                    "Projects.CompanyReferentMail = CompanyReferents.CompanyReferentMail AND " +
                    "Projects.isDone = " + isDone + " AND " +
                    "ProjectID LIKE '%" + pName + "%';";

                return myDatabaseCommander.ExecuteArray(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Get all the projects.
        /// </summary>
        /// <param name="isDone">Is the project done(1/0)?</param>
        /// <returns></returns>
        public static DataTable getAllProjects(int isDone){
            try
            {
                String query =
                    "SELECT ProjectID, CompanyName, ProjectName, ProjectCity, ProjectAddr, " +
                    "CompanyReferentName, DateCreated " +
                    "FROM Projects,Companies,CompanyReferents " +
                    "WHERE Projects.CompanyID = Companies.CompanyID AND " +
                    "Projects.CompanyReferentMail = CompanyReferents.CompanyReferentMail AND " +
                    "Projects.isDone = " + isDone + ";";

                return myDatabaseCommander.ExecuteArray(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Empty constructor.
        /// </summary>
        public Project()
        {
        }

        /// <summary>
        /// Gets a single Project with a certain ProjId.
        /// </summary>
        /// <param name="projID">Key of project.</param>
        public Project(string projID)
        {
            try
            {
                String query =
                    "SELECT * FROM Projects,Companies,CompanyReferents " +
                    "WHERE Projects.CompanyID = Companies.CompanyID AND " +
                    "Projects.CompanyReferentMail = CompanyReferents.CompanyReferentMail AND " +
                    "Projects.ProjectID = '" + projID + "' " + 
                    "ORDER BY DateCreated DESC";

                DataTable dt = myDatabaseCommander.ExecuteArray(query);
                foreach (DataRow r in dt.Rows)
                {
                    this.CompanyID = r["CompanyID"].ToString();
                    this.CompanyReferentMail = r["CompanyReferentMail"].ToString();
                    this.ProjectAddr = r["ProjectAddr"].ToString();
                    this.ProjectCity = r["ProjectCity"].ToString();
                    this.ProjectID = r["ProjectID"].ToString();
                    this.ProjectName = r["ProjectName"].ToString();
                    this.DateCreated = r["DateCreated"].ToString();

                }//foreach
            }
            catch (Exception e)
            {
                throw e;
            }
        }//constructor

        /// <summary>
        /// Adds new project into database.
        /// </summary>
        public void insertProject()
        {
            try
            {
                String query =
                    "INSERT INTO Projects(ProjectID, ProjectName, ProjectCity, ProjectAddr, " +
                    "CompanyReferentMail, CompanyID, isDone) " +
                    "VALUES(" +
                    "'" + this.ProjectID + "', " +
                    "'" + this.ProjectName + "', " +
                    "'" + this.ProjectCity + "', " +
                    "'" + this.ProjectAddr + "', " +
                    "'" + this.CompanyReferentMail + "', " +
                    "'" + this.CompanyID + "', " +
                    0 +
                    ");";

                myDatabaseCommander.ExecuteNonQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

    }
}
